/*
 * PurchaseOrderDAO.java
 *
 * Created on March 29, 2007, 10:50 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.samlam;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 *
 * @author BrianB
 */
public class PurchaseOrderDAO {
    
    //singleton pattern
    private static PurchaseOrderDAO instance = null;
    
    /** Creates a new instance of PurchaseOrderAO */
    public PurchaseOrderDAO() {
    }
    
    /**
     * enforcement of the singleton pattern
     * @return the instance which will be used when the DAO is used
     */
    public static synchronized PurchaseOrderDAO getInstance() {
        if (instance == null) {
            instance = new PurchaseOrderDAO();
        }
        return instance;
    }
    
    
    //////////////////////////
    //CREATE
    /**
     * This method is used to create a business object of type PurchaseOrder.
     * @return PurchaseOrderBO
     * @throws edu.byu.isys413.estrogen.data.DataException if the GUID cannot be generated
     */
    public PurchaseOrder create() throws DataException {
        String id = null;
        try{
            id = GUID.generate();
        } catch (Exception e) {
            throw new DataException("Could not generate new Purchase Order", e);
        }
        PurchaseOrder order = new PurchaseOrder(id);
        Cache c = Cache.getInstance();
        c.put(order.getPoguid(), order);
        return order;
    }
   
     
     
    //////////////////////////
    //READ
    /**
     * This method reads the PurchaseOrder object from the guid.
     * @param id the GUID for a particular PurchaseOrder to be read
     * @return PurchaseOrderBO with the matching id
     * @throws edu.byu.isys413.estrogen.data.DataException if there is an error with the SQL or the connection
     */
    public synchronized PurchaseOrder read(String id) throws DataException {
               
       PurchaseOrder order = (PurchaseOrder)Cache.getInstance().get(id);
        
        if (order == null) {
        Connection conn = null;
            try {
                conn = ConnectionPool.getInstance().get();

                order = read(id, conn);

                conn.commit();
                ConnectionPool.getInstance().release(conn);                
            }
            catch (DataException e) {
                throw new DataException("Could not get a connection to the database.");
            }
            catch (SQLException e) {
                //rollback
                try {
                    conn.rollback();
                    ConnectionPool.getInstance().release(conn);
                }
                catch (SQLException e2) {
                    throw new DataException("ERROR: Could not even release the connection", e2);
                }
                throw new DataException("Could not retrieve record for id=" + id, e);
            }
        }
        
        return order;
    }
    
    /**
     * This method reads the PurchaseOrder object with the id and a connection
     * @param id the GUID for a particular PurchaseOrder to be read
     * @param conn the connection to use for the reading
     * @return PurchaseOrderBO with the specified id
     * @throws edu.byu.isys413.estrogen.data.DataException if there is any error in the reading
     * @throws java.sql.SQLException if there is any error in the SQL
     */
    public synchronized PurchaseOrder read(String id, Connection conn) throws DataException, SQLException {                  //will also create a BO, but it's pulling it off the database and creating the object to be used
        PurchaseOrder order = (PurchaseOrder)Cache.getInstance().get(id);
        
        if (order == null) {
                   
            try {   
                PreparedStatement SELECT = conn.prepareStatement("SELECT * FROM APP.purchaseorder WHERE poguid = ?");
                SELECT.setString(1, id);
                ResultSet rs = SELECT.executeQuery();
                
                conn.commit();
                
                if (rs.next()) {
                    order = new PurchaseOrder(id);
                    order.setOrderlines(OrderLineDAO.getInstance().getAllLinesForOrder(order, conn));
                    Cache.getInstance().put(id, order);
                    
                    order.setAlreadyInDB(true);
                }
                else {
                    throw new DataException("Object was not found in the database.");
                }
                
            }catch (SQLException e) {
                    conn.rollback();
                    throw new DataException("Could not retrieve record for id=" + id, e);
            }catch (Exception e) {
                throw new DataException("error here...", e);
            }
        }
        
        return order;
    }
    
    /**
     * This method gets the connection and puts the PurchaseOrder into the database.
     * @param po the PurchaseOrderBO to be saved
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any errors while processing
     * @throws java.sql.SQLException if there are any errors executing the SQL
     */
    public synchronized void save(PurchaseOrder order) throws SQLException, DataException {
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            System.out.println("Going to save with conn");
            save(order, conn);
            
            conn.commit();
            ConnectionPool.getInstance().release(conn);
        }
        catch (DataException e) {
            conn.rollback();
            throw new DataException("Could not connect to the database.", e);
        }
        
    }
    
    /**
     * This method puts the PurchaseOrder into the database, either inserting
     * or updating depending on whether it exists in the database already.
     * @param order the PurchaseOrderBO to be saved
     * @param conn the connection to be used
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any errors while processing
     * @throws java.sql.SQLException if there are any errors executing the SQL
     */
    synchronized void save(PurchaseOrder order, Connection conn) throws SQLException, DataException {
        if (order.isAlreadyInDB()){
            update(order, conn);    
        }   
        else {
            insert(order, conn);
            
        }
        order.setDirty(false);
    }
    
     /**
     * This method saves the updated PurchaseOrderto the database if it already exists.
     * @param order the PurchaseOrderBO to be saved
     * @param conn the connection passed from the save method
     * @throws java.sql.SQLException if there are any problems executing the SQL
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any other errors while executing the update
     */
    void update(PurchaseOrder order, Connection conn) throws SQLException, DataException {
        try {
            System.out.println("POGUID:" + order.getPoguid());
            PreparedStatement UPDATE= conn.prepareStatement("UPDATE APP.purchaseorder SET vguid = ? WHERE poguid = ?");
            UPDATE.setString(1, order.getVendor().getVguid());
            UPDATE.setString(2, order.getPoguid());
            
            UPDATE.executeUpdate();            
            System.out.println("Update executed");
            UPDATE.close();
        }
        catch (SQLException e) {
            conn.rollback();
            throw new DataException("Could not perform the update on the Transaction", e);
        }
    }
    
    /**
     * This method saves the PurchaseOrderinto the database if it did not previously 
     * exist.
     * @param order the PurchaseOrderBO to be saved
     * @param conn the connection passed from the save method
     * @throws java.sql.SQLException if there are any problems executing the SQL
     * @throws edu.byu.isys413.estrogen.data.DataException if there are any other errors while executing the insert
     */
    public void insert(PurchaseOrder order, Connection conn) throws SQLException, DataException {
        try {
            PreparedStatement INSERT = conn.prepareStatement("INSERT INTO APP.purchaseorder VALUES (?, ?)" );
            INSERT.setString(1, order.getPoguid());
            INSERT.setString(2, order.getVendor().getVguid());     
            
            INSERT.executeUpdate();
            INSERT.close();
            
            order.setAlreadyInDB(true);
            
        }
        catch (SQLException e) {
            conn.rollback();
            throw new DataException("Could not insert the PurchaseOrder into the database", e);
        }
    }
    
    
    /////////////////////////////////DELETE//////////////////////////////////////////////////
    //A delete method was intentionally omitted here for business reasons--we would like
    //to keep a record of all purchase orders Because they are attached to other essential elements
    //that should not be deleted from the database.  Rather than deleting them completely,
    //we might instead build in a "deleted" boolean variable where we can flag a customer if they are 
    //no longer active.
    
}

					